﻿using System;
using System.Collections.Generic;
using System.Text;

namespace Sofire.Data
{
    /// <summary>
    /// 基于 ORACLE 的分页。
    /// </summary>
    public class OraclePagination : PaginationBase
    {
        #region Format

        private const string PageFormat = @"SELECT * FROM
     (
     SELECT  rownum {1},a.*
     FROM
          (
         {0}
          ) a
     WHERE rownum <= {3}
     ) b
WHERE {1} > {2}";

        #endregion

        /// <summary>
        /// 初始化 <see cref="Sofire.Data.OraclePagination"/> 的实例。
        /// </summary>
        public OraclePagination() { }

        /// <summary>
        /// 初始化 <see cref="Sofire.Data.OraclePagination"/> 的实例。
        /// </summary>
        /// <param name="page">页码。</param>
        /// <param name="pageSize">页大小。</param>
        public OraclePagination(int page, int pageSize) : base(page, pageSize) { }

        /// <summary>
        /// 初始化 <see cref="Sofire.Data.OraclePagination"/> 的实例。
        /// </summary>
        /// <param name="command">查询的命令。</param>
        public OraclePagination(ExecuteCommand command)
        {
            this.Command = command;
        }

        /// <summary>
        /// 初始化 <see cref="Sofire.Data.OraclePagination"/> 的实例。
        /// </summary>
        /// <param name="command">查询的命令。</param>
        /// <param name="page">页码。</param>
        /// <param name="pageSize">页大小。</param>
        public OraclePagination(ExecuteCommand command, int page, int pageSize)
            : base(page, pageSize)
        {
            this.Command = command;
        }

        private string _RowNumberName = "RN";
        /// <summary>
        /// 获取默认页码字段的列名。
        /// </summary>
        public string RowNumberName
        {
            get
            {
                return this._RowNumberName;
            }
            set
            {
                this._RowNumberName = value;
            }
        }

        private ExecuteCommand _Command;
        /// <summary>
        /// 设置或获取查询的命令。
        /// </summary>
        public ExecuteCommand Command
        {
            get
            {
                return this._Command;
            }
            set
            {
                this._Command = value;
            }
        }

        /// <summary>
        /// 根据指定的数据库引擎获取分页数据。
        /// </summary>
        /// <param name="engine">数据库引起。</param>
        /// <returns>返回已分页的页码。</returns>
        public override TableResult Execute(QueryEngineBase engine)
        {
            if(engine == null) throw new ArgumentNullException("engine");
            if(this._Command == null) throw new ArgumentNullException("_Command");
            var pageCommand = this._Command.Clone() as ExecuteCommand;

            var start = (this.Page - 1) * this.PageSize;
            var end = this.Page * this.PageSize;
            pageCommand.CommandText = string.Format(PageFormat
                , pageCommand.CommandText
                , this._RowNumberName
                , start
                , end);
            return engine.ExecuteTable(pageCommand);
        }

        /**************** MSSQL****************
         * http://topic.csdn.net/u/20080627/12/4a404bb1-6667-466a-925a-4d7bf690695e.html
CREATE PROC sp_PageView   
@sql         ntext,     --要执行的sql语句
@PageCurrent int=1,     --要显示的页码
@PageSize    int=10,    --每页的大小
@PageCount   int OUTPUT --总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
--初始化分页游标
EXEC sp_cursoropen 
    @cursor=@p1 OUTPUT,
    @stmt=@sql,
    @scrollopt=1,
    @ccopt=1,
    @rowcount=@PageCount OUTPUT

--计算总页数
IF ISNULL(@PageSize,0)<1 
    SET @PageSize=10
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount
    SET @PageCurrent=1
ELSE
    SET @PageCurrent=(@PageCurrent-1)*@PageSize+1

--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize

--关闭分页游标
EXEC sp_cursorclose @p1
         
         */
    }
}
